python sqlite

import sqlite3

# Create a database and open the database.
# If the database already exists just opens the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Create a users table if the table does not exists
c.execute('''CREATE TABLE IF NOT EXISTS users(name TEXT, age INTEGER)''')
# commit changes and close database connect
conn.commit()
conn.close()

# Insert values with parameter queries to prevent sql injections
conn = sqlite3.connect('users.db')
c = conn.cursor()
name = "John"
number = 20
c.execute("INSERT INTO users VALUES (?, ?)", (name, number))
# commit changes and close database connect
conn.commit()
conn.close()

# Insert many values with parameter queries to prevent sql injections
users = [
   ("James", 10),
   ("Smith", 21),
   ("Jerome", 11)
]
conn = sqlite3.connect('users.db')
c = conn.cursor()
c.executemany("INSERT INTO users VALUES (?, ?)", users)
# commit changes and close database connect
conn.commit()
conn.close()

# Print values from table
conn = sqlite3.connect('users.db')
c = conn.cursor()

for row in c.execute('SELECT * FROM users'):
   print(row)

# list comprehension to store all names and ages in separate lists
names = [row[0] for row in c.execute('SELECT * FROM users')]
ages = [row[1] for row in c.execute('SELECT * FROM users')]
# list comprehension store all names and ages in a list
combined = [f"Name: {row[0]} - Age: {row[1]}" for row in c.execute('SELECT * FROM users')]
# print names and ages list
print(names, ages)
# print name and ages command list
print(combined)
# close connection
conn.close()

# Print all rows in a list of tuples using fetchall
conn = sqlite3.connect('users.db')
c = conn.cursor()
result = c.execute("SELECT * FROM users")
print(result.fetchall())
conn.close()

# output
# ('John', 20)
# ('James', 10)
# ('Smith', 21)
# ('Jerome', 11)
# ['John', 'James', 'Smith', 'Jerome'] [20, 10, 21, 11]
# ['Name: John - Age: 20', 'Name: James - Age: 10', 'Name: Smith - Age: 21', 'Name: Jerome - Age: 11']
# [('John', 20), ('James', 10), ('Smith', 21), ('Jerome', 11)

import sqlite3
import os.path

#Connection to the DB
try:
    # Make sure to find the file.db in the script directory
    BASE_DIR = os.path.dirname(os.path.abspath(__file__)) 
    db_path = os.path.join(BASE_DIR, "sqlite.db")
    conn = sqlite3.connect(db_path)

except sqlite3.Error as error:
    print("Failed to read data from sqlite table", error)



# Execute query on the sqlite DB
cur = conn.cursor()
cur.execute("SELECT * FROM tasks")

# Print everything from a table
rows = cur.fetchall()
for row in rows:
        print(row)
    
# Update field 
conn.execute("""UPDATE tasks SET name = \'jhon\'
 where id = 1""")

# close the DB connection 
conn.close() 

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')

print "Opened database successfully";

import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn


def create_project(conn, project):
    """
    Create a new project into the projects table
    :param conn:
    :param project:
    :return: project id
    """
    sql = ''' INSERT INTO projects(name,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowid


def create_task(conn, task):
    """
    Create a new task
    :param conn:
    :param task:
    :return:
    """

    sql = ''' INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
              VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowid


def main():
    database = r"C:\sqlite\db\pythonsqlite.db"

    # create a database connection
    conn = create_connection(database)
    with conn:
        # create a new project
        project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30');
        project_id = create_project(conn, project)

        # tasks
        task_1 = ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02')
        task_2 = ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')

        # create tasks
        create_task(conn, task_1)
        create_task(conn, task_2)


if __name__ == '__main__':
    main()
Code language: Python (python)